﻿
CREATE TABLE [dbo].[Order] (
[Id] UniqueIdentifier NOT NULL,
[Title] NVarChar(250) NULL,
[Summary] NVarChar(MAX) NULL,
[Due] DateTime NULL,
[Created] DateTime NOT NULL,
[CurrentLastStageId] UniqueIdentifier NULL,
[CurrentLastComponentId] UniqueIdentifier NULL,
[ContactId] UniqueIdentifier NULL,
[Status] INT NOT NULL
)
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [pk_Order_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

CREATE TABLE [dbo].[Contact](
	[Id] UniqueIdentifier NOT NULL,
	[Title] VARCHAR(10) NULL,
	[FirstName] VARCHAR(100) NULL,
	[LastName] VARCHAR(100) NULL,
	[SearchName] VARCHAR(250) NULL,
	[Email] VARCHAR(250) NULL,
	[Facebook] VARCHAR(250) NULL,
	[PhoneNumber] VARCHAR(15) NULL,
	[HouseNameNumber] VARCHAR(25) NULL,
	[Street] VARCHAR(250) NULL,
	[Town] VARCHAR(250) NULL,
	[County] VARCHAR(250) NULL,
	[PostCode] VARCHAR(10) NULL
)
ALTER TABLE [ButterflyCakes].[dbo].[Contact] ADD CONSTRAINT [pk_Contact_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO dbo.Contact ( Id , Title , FirstName , LastName , SearchName ,Email ,Facebook ,PhoneNumber ,HouseNameNumber ,Street ,Town ,County ,PostCode)VALUES  ( NEWID() ,'Mr' ,'Simon' ,'Potter' ,'Mr Simon Potter' ,'simon.potter78@yahoo.com' ,'simon.potter@facebook.com' ,'07919 525952' ,'18' ,'Arundel Street, Hindley' ,'Wigan' ,'Lancashire' ,'WN2 3LT')
INSERT INTO dbo.Contact ( Id , Title , FirstName , LastName , SearchName ,Email ,Facebook ,PhoneNumber ,HouseNameNumber ,Street ,Town ,County ,PostCode)VALUES  ( NEWID() ,'Mrs' ,'Gemma' ,'Potter' ,'Mrs Gemma Potter' ,'gem_gemz@hotmail.com' ,'gemma.potter@facebook.com' ,'01942 253668' ,'18' ,'Arundel Street, Hindley' ,'Wigan' ,'Lancashire' ,'WN2 3LT')


CREATE TABLE [dbo].[Component] (
[Id] UniqueIdentifier NOT NULL,
[OrderId] UniqueIdentifier NOT NULL,
[ComponentType] Int NULL,
[Title] NVarChar(250) NULL,
[IsCarved] bit NULL,
[Portions] Int NULL,
[Height] Int NULL,
[HeightType] Int NULL,
[NumberOfFlavours] Int NULL,
[NumberOfStripedCakes] Int NULL,
[ShapeBuilderActiveCellString] VARCHAR(MAX) NULL,
[Quantity] INT NULL,
[ButtercreamId] UniqueIdentifier NULL,
[FillingColour] VARCHAR(10) NULL,
[GanacheId] UniqueIdentifier NULL,
[PreserveId] UniqueIdentifier NULL,
[CoveringType] INT NULL,
[CoveringColour] VARCHAR(10) NULL,
[FinishType] INT NULL
)
ALTER TABLE [dbo].[Component] ADD CONSTRAINT [pk_Component_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]


CREATE TABLE [dbo].[ComponentFlavour](
	[Id] UniqueIdentifier NOT NULL,
	[ComponentId] UniqueIdentifier NOT NULL,
	[CakeFlavourTypeId] UniqueIdentifier NULL,
	[CakeFlavourId] UniqueIdentifier NULL
)
ALTER TABLE [dbo].[ComponentFlavour] ADD CONSTRAINT [pk_ComponentFlavour_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

CREATE TABLE [dbo].[ComponentSize](
	[Id] UniqueIdentifier NOT NULL,
	[ComponentId] UniqueIdentifier NOT NULL,
	[Shape] Int NULL,
	[Size] Int NULL,
	[Width] Int NULL,
	[CustomShapeDetail] NVarChar(MAX) NULL,
	[Portions] Int NULL
)
ALTER TABLE [dbo].[ComponentSize] ADD CONSTRAINT [pk_ComponentSize_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]


CREATE TABLE [dbo].[EntityStage] (
[Id] UniqueIdentifier NOT NULL,
[ComponentId] UniqueIdentifier NOT NULL,
[Created] DateTime NOT NULL,
[Stage] Int NOT NULL,
[OrderId] UniqueIdentifier NOT NULL,
[Title] NVarChar(250) NOT NULL,
[PreviousStageId] UniqueIdentifier NULL,
[Index] Int NOT NULL
)
ALTER TABLE [dbo].[EntityStage] ADD CONSTRAINT [pk_EntityStage_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]



CREATE TABLE [dbo].[Attachments] (
	[Id] UniqueIdentifier NOT NULL,
	[OrderId] UniqueIdentifier NULL,
	[FileContent] VARBINARY(MAX) NOT NULL,
	[FileName] VARCHAR(500) NOT NULL,
	[FileExtension] VARCHAR(25) NOT NULL,
	[IsImage] bit NOT NULL,
	[Description] VARCHAR(500) NULL,
	[MimeType] VARCHAR(100) NOT NULL
)
ALTER TABLE [dbo].[Attachments] ADD CONSTRAINT [pk_Attachments_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]



CREATE TABLE [dbo].[Diagram](
	[Id] UniqueIdentifier NOT NULL,
	[ComponentId] UniqueIdentifier NOT NULL,
	[OrderId] UniqueIdentifier NOT NULL,
	[Html] VARCHAR(MAX) NULL,
	[Description] VARCHAR(500) NULL
)
ALTER TABLE [dbo].[Diagram] ADD CONSTRAINT [pk_Diagram_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]





CREATE TABLE [dbo].[CakeFlavourType] (
	[Id] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100),
	[IsCompatibleWithCupCakes] Bit NOT NULL,
	[IsCompatibleWith3DCakes] Bit NOT NULL,
	[IsCompatibleWithGiantCakes] Bit NOT NULL,
	[CanCarve] Bit NOT NULL
)
ALTER TABLE [dbo].[CakeFlavourType] ADD CONSTRAINT [pk_CakeFlavourType_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Vanilla', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Traditional Fruit', 0, 0, 0, 0)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Sponge Fruit', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Chocolate', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Lemon', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Chocolate Chip', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Coffee', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Red Velvet', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Syrup', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Carrot', 1, 1, 1, 1)
INSERT INTO [dbo].[CakeFlavourType] ([Id], [Name], [IsCompatibleWithCupCakes], [IsCompatibleWith3DCakes], [IsCompatibleWithGiantCakes], [CanCarve]) VALUES(NEWID(), 'Flower', 1, 1, 1, 1)



CREATE TABLE [dbo].[CakeFlavour](
	[Id] UniqueIdentifier NOT NULL,
	[TypeId] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100) NOT NULL,
	[CanCarve] Bit NOT NULL
)
ALTER TABLE [dbo].[CakeFlavour] ADD CONSTRAINT [pk_CakeFlavour_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Very Vanilla', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Vanilla'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Cheeky Chocolate', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Madagascan Vanilla', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Vanilla'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Indulgent Chocolate', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Chocolate Kirsch', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Butterscotch', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Syrup'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Maple', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Syrup'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Summer Strawberry', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Sponge Fruit'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Toffee', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Syrup'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Red Velvet', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Red Velvet'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Carrot', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Carrot'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Vanilla Chiffon', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Vanilla'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Rose Chiffon', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Flower'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Violet Chiffon', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Flower'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Fruit', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Traditional Fruit'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Espresso & Walnut', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Coffee'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Cappuccino', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Coffee'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Lemon Zing', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Lemon'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Lemon Drizzle', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Lemon'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Wild at Heart (Chocolate and Orange)', [Id], 1 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'White Chocolate Chip', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate Chip'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Milk Chocolate Chip', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate Chip'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Dark Chocolate Chip', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Chocolate Chip'
INSERT INTO [dbo].[CakeFlavour] ([Id], [Name], [TypeId], [CanCarve]) SELECT NEWID(), 'Cherry', [Id], 0 FROM dbo.CakeFlavourType WHERE [Name] = 'Sponge Fruit'





CREATE TABLE [dbo].[FillingType](
	[Id] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100) NOT NULL
)
ALTER TABLE [dbo].[FillingType] ADD CONSTRAINT [pk_FillingType_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[FillingType] ([Id], [Name]) VALUES (NEWID(), 'Buttercream')
INSERT INTO [dbo].[FillingType] ([Id], [Name]) VALUES (NEWID(), 'Ganache')
INSERT INTO [dbo].[FillingType] ([Id], [Name]) VALUES (NEWID(), 'Preserve')




CREATE TABLE [dbo].[FillingFlavour](
	[Id] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100) NOT NULL,
	[FillingTypeId] UniqueIdentifier NOT NULL
)
ALTER TABLE [dbo].[FillingFlavour] ADD CONSTRAINT [pk_FillingFlavour_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Plain', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Buttercream'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Vanilla', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Buttercream'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Chocolate', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Buttercream'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Lemon', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Buttercream'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Dark Chocolate', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Ganache'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Milk Chocolate', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Ganache'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'White Chocolate', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Ganache'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Apricot', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Preserve'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Strawberry', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Preserve'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Rasberry', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Preserve'
INSERT INTO [dbo].[FillingFlavour] ([Id],[Name],[FillingTypeId]) SELECT NEWID(), 'Cherry', ft.[Id] FROM [dbo].[FillingType] AS ft WHERE ft.[Name] = 'Preserve'




CREATE TABLE [dbo].[IncompatibleFillingForCake](
	[Id] UniqueIdentifier NOT NULL,
	[CakeFlavourId] UniqueIdentifier NOT NULL,
	[FillingFlavourId] UniqueIdentifier NOT NULL
)
ALTER TABLE [dbo].[IncompatibleFillingForCake] ADD CONSTRAINT [pk_IncompatibleFillingForCake_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Zing'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'Dark Chocolate'
INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Zing'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'Milk Chocolate'
INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Zing'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'White Chocolate'
INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Drizzle'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'Dark Chocolate'
INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Drizzle'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'Milk Chocolate'
INSERT INTO [dbo].[IncompatibleFillingForCake] ([Id], [CakeFlavourId], [FillingFlavourId]) SELECT NEWID(), (SELECT [Id] FROM dbo.CakeFlavour WHERE Name = 'Lemon Drizzle'), ff.[Id] FROM [dbo].[FillingFlavour] ff WHERE ff.Name = 'White Chocolate'




CREATE TABLE [dbo].[IncompatibleFillingForFilling](
	[Id] UniqueIdentifier NOT NULL,
	[BaseFillingFlavour] UniqueIdentifier NOT NULL,
	[FillingFlavour] UniqueIdentifier NOT NULL
)
ALTER TABLE [dbo].[IncompatibleFillingForFilling] ADD CONSTRAINT [pk_IncompatibleFillingForFilling_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]



CREATE TABLE [dbo].[DecorationType](
	[Id] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100) NULL
)
ALTER TABLE [dbo].[DecorationType] ADD CONSTRAINT [pk_DecorationType_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[DecorationType] ([Id], [Name]) VALUES (NEWID(), 'Fondant Flowers')
INSERT INTO [dbo].[DecorationType] ([Id], [Name]) VALUES (NEWID(), 'Buttercream Swirls')


CREATE TABLE [dbo].[Decoration](
	[Id] UniqueIdentifier NOT NULL,
	[DecorationTypeId] UniqueIdentifier NOT NULL,
	[Name] VARCHAR(100) NULL,
	[Description] VARCHAR(MAX) NULL,
	[CanSetColour] BIT NOT NULL
)
ALTER TABLE [dbo].[Decoration] ADD CONSTRAINT [pk_Decoration_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

INSERT INTO [dbo].[Decoration] ([Id], [DecorationTypeId], [Name], [Description], [CanSetColour]) SELECT NEWID(), dt.[Id], 'Standard Rose', 'Standard size fondant rose', 1 FROM [dbo].[DecorationType] dt WHERE dt.[Name] = 'Fondant Flowers'
INSERT INTO [dbo].[Decoration] ([Id], [DecorationTypeId], [Name], [Description], [CanSetColour]) SELECT NEWID(), dt.[Id], 'Large Rose', 'Larger size fondant rose', 1 FROM [dbo].[DecorationType] dt WHERE dt.[Name] = 'Fondant Flowers'
INSERT INTO [dbo].[Decoration] ([Id], [DecorationTypeId], [Name], [Description], [CanSetColour]) SELECT NEWID(), dt.[Id], 'Basic Swirl', 'Basic cup cake swirl', 1 FROM [dbo].[DecorationType] dt WHERE dt.[Name] = 'Buttercream Swirls'
INSERT INTO [dbo].[Decoration] ([Id], [DecorationTypeId], [Name], [Description], [CanSetColour]) SELECT NEWID(), dt.[Id], 'Rose', 'Cup cake piped rose', 1 FROM [dbo].[DecorationType] dt WHERE dt.[Name] = 'Buttercream Swirls'
INSERT INTO [dbo].[Decoration] ([Id], [DecorationTypeId], [Name], [Description], [CanSetColour]) SELECT NEWID(), dt.[Id], 'Spiky piped thing', 'I cant remember the name of it', 1 FROM [dbo].[DecorationType] dt WHERE dt.[Name] = 'Buttercream Swirls'



CREATE TABLE [dbo].[ComponentDecoration](
	[Id] UniqueIdentifier NOT NULL,
	[ComponentId] UniqueIdentifier NOt NULL,
	[PresetDecorationId] UniqueIdentifier NULL,
	[Description] VARCHAR(MAX) NULL,
	[Count] INT NOT NULL,
	[Colour] VARCHAR(10) NULL
)
ALTER TABLE [dbo].[ComponentDecoration] ADD CONSTRAINT [pk_ComponentDecoration_Id] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]